﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace SuperSqlOperation.SqlServer
{
    /// <summary>
    /// 选择数据
    /// </summary>
    static class SelectSqlExecute
    {
        /// <summary>
        /// 获得全表所有数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static async Task<IEnumerable<T>> SelectExecute<T>(this IDbConnection conn)
        {

            List<T> dataList = new List<T>();
            Type t = typeof(T);
            PropertyInfo[] propertyInfo = t.GetProperties();

            string tableName = t.GetDataTableNameByAttr();
            StringBuilder cloumnStr = new StringBuilder();
            foreach (PropertyInfo property in propertyInfo)
            {
                if (property.GetDataIgnoreByAttr()) continue;  //过滤掉特性定义的需要忽略的属性
                if (!property.GetDataWriteByAttr()) continue;  //过滤掉特性定义的是否可写的属性 定义为false则忽略 
                if (property.PropertyType.IsConstructedGenericType ||
                    (!property.PropertyType.IsPrimitive && !property.PropertyType.Equals(typeof(string)) &&
                    property.PropertyType.IsClass)) continue;

                string columnName = property.GetDataColumnByAttr();
                cloumnStr.Append(columnName + ",");
            }


            using (conn)
            {
                SqlCommand comm = conn.CreateCommand() as SqlCommand;
                comm.CommandText = string.Format("SELECT {0} FROM {1}", cloumnStr.ToString().TrimEnd(','), tableName);
                conn.Open();
                // SqlDataReader reader =  comm.ExecuteReader(CommandBehavior.CloseConnection);
                // SqlDataReader reader = await Task.Run(() => comm.ExecuteReader(CommandBehavior.CloseConnection));
                SqlDataReader reader = comm.ExecuteReaderAsync(CommandBehavior.CloseConnection).Result as SqlDataReader;
                while (reader.Read())
                {
                    object obj = Activator.CreateInstance(t);
                    foreach (PropertyInfo property in propertyInfo)
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            if (!reader.GetName(i).Equals(property.Name))
                            { continue; }

                            if (reader[property.Name] == null)
                            { property.SetValue(obj, DBNull.Value); break; }
                            property.SetValue(obj, reader[property.Name]);
                            break;
                        }
                    }
                    dataList.Add((T)obj);
                    // dataList.Add((T)GetClassProperty(reader, t));
                }
                reader.Close();
                conn.Close();
                IEnumerable<T> dataEnum = dataList;
                return dataEnum;
            }
        }

        public static async Task<IEnumerable<T>> SelectExecute<T>(this IDbConnection conn, int id)
        {

            List<T> dataList = new List<T>();
            Type t = typeof(T);
            PropertyInfo[] propertyInfo = t.GetProperties();

            string keyName = string.Empty;
            string tableName = t.GetDataTableNameByAttr();
            StringBuilder cloumnStr = new StringBuilder();
            foreach (PropertyInfo property in propertyInfo)
            {
                if (property.GetDataIgnoreByAttr()) continue;  //过滤掉特性定义的需要忽略的属性
                if (!property.GetDataWriteByAttr()) continue;  //过滤掉特性定义的是否可写的属性 定义为false则忽略 
                if (property.PropertyType.IsConstructedGenericType ||
                    (!property.PropertyType.IsPrimitive && !property.PropertyType.Equals(typeof(string)) &&
                    property.PropertyType.IsClass)) continue;

                string columnName = property.GetDataColumnByAttr();
                cloumnStr.Append(columnName + ",");

                if (property.GetDataKeyByAttr())  //此处获得主键列名
                { keyName = property.GetDataColumnByAttr(); }
            }
            string whereStr = keyName + "= @" + keyName;


            using (conn)
            {
                SqlCommand comm = conn.CreateCommand() as SqlCommand;
                comm.CommandText = string.Format("SELECT {0} FROM {1} WHERE {2}", cloumnStr.ToString().TrimEnd(','), tableName, whereStr);
                comm.Parameters.AddWithValue("@" + keyName, id);
                conn.Open();
                // SqlDataReader reader =  comm.ExecuteReader(CommandBehavior.CloseConnection);
               // SqlDataReader reader = await Task.Run(() => comm.ExecuteReader(CommandBehavior.CloseConnection));
                SqlDataReader reader = comm.ExecuteReaderAsync(CommandBehavior.CloseConnection).Result as SqlDataReader;
                while (reader.Read())
                {
                    object obj = Activator.CreateInstance(t);
                    foreach (PropertyInfo property in propertyInfo)
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            if (!reader.GetName(i).Equals(property.Name))
                            { continue; }

                            if (reader[property.Name] == null)
                            { property.SetValue(obj, DBNull.Value); break; }
                            property.SetValue(obj, reader[property.Name]);
                            break;
                        }
                    }
                    dataList.Add((T)obj);
                    // dataList.Add((T)GetClassProperty(reader, t));
                }
                reader.Close();
                conn.Close();
                IEnumerable<T> dataEnum = dataList;
                return dataEnum;
            }
        }

        public static async Task<IEnumerable<T>> SelectExecute<T>(this IDbConnection conn, Dictionary<string, object> conds, string linkStr)
        {
            linkStr = " " + linkStr + " ";
            List<T> dataList = new List<T>();
            StringBuilder cloumnStr = new StringBuilder();
            StringBuilder whereStr = new StringBuilder();
            List<SqlParameter> par = new List<SqlParameter>();
            Type t = typeof(T);
            PropertyInfo[] propertyInfo = t.GetProperties();
            string tableName = t.GetDataTableNameByAttr();

            foreach (PropertyInfo property in propertyInfo)
            {
                if (property.GetDataIgnoreByAttr()) continue;  //过滤掉特性定义的需要忽略的属性
                if (!property.GetDataWriteByAttr()) continue;  //过滤掉特性定义的是否可写的属性 定义为false则忽略 
                if (property.PropertyType.IsConstructedGenericType ||
                    (!property.PropertyType.IsPrimitive && !property.PropertyType.Equals(typeof(string)) &&
                    property.PropertyType.IsClass)) continue;

                string columnName = property.GetDataColumnByAttr();
                cloumnStr.Append(columnName + ",");
            }

            foreach (var cond in conds)
            {
                string columnName = cond.Key;
                object columnValue = cond.Value;
                whereStr.Append(columnName + "= @" + columnName + linkStr);
                par.Add(new SqlParameter("@" + columnName, columnValue));
            }

            //-----------------
            IEnumerable<T> dataEnum;
            using (conn)
            {
                SqlCommand comm = conn.CreateCommand() as SqlCommand;

                comm.CommandText = string.Format("SELECT {0} FROM {1} WHERE {2}",
                    cloumnStr.ToString().TrimEnd(','),
                    tableName,
                    whereStr.ToString().Substring(0, whereStr.ToString().LastIndexOf(linkStr)));
                comm.Parameters.AddRange(par.ToArray());

                conn.Open();
                // SqlDataReader reader =  comm.ExecuteReader(CommandBehavior.CloseConnection);
                //SqlDataReader reader = await Task.Run(() => comm.ExecuteReader(CommandBehavior.CloseConnection)); 
                SqlDataReader reader = comm.ExecuteReaderAsync(CommandBehavior.CloseConnection).Result as SqlDataReader;
                while (reader.Read())
                {
                    object obj = Activator.CreateInstance(t);
                    foreach (PropertyInfo property in propertyInfo)
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            if (!reader.GetName(i).Equals(property.Name))
                            { continue; }

                            if (reader[property.Name] == null)
                            { property.SetValue(obj, DBNull.Value); break; }
                            property.SetValue(obj, reader[property.Name]);
                            break;
                        }
                    }
                    dataList.Add((T)obj);
                    // dataList.Add((T)GetClassProperty(reader, t));
                }

                reader.Close();
                conn.Close();
                dataEnum = dataList;
            }
            return dataEnum;
        }

    }

}
